using System;
using System.Configuration;
using System.Data;
using DataSync.Properties;
using MySql.Data.MySqlClient;

namespace DataSync.DataAccess.Staff
{
    public class StaffDataAccess
    {
        private MySqlConnection _MySqlConnection;
        private MySqlCommand _MySqlCommand;
        private MySqlDataAdapter _MySqlDataAdapter;        
        private string _ConnectionString;
        private string _QueryString;

        public DataTable GetStaffs()
        {
            var dataTable = new DataTable();

            _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringCentral"].ToString();
            _MySqlConnection = new MySqlConnection(_ConnectionString);
            _MySqlConnection.Open();

            _QueryString = 
                "SELECT" +
	            "   type_contract," + 
	            "   employee_status," + 
	            "   employee_type_id," + 	
	            "   id," + 
	            "   name," + 
	            "   namekh," + 
	            "   photo," + 
	            "   position," + 
	            "   null as position_title," + 
	            "   sex," + 
	            "   shift," + 
	            "   title " +
                "FROM" + 
                "   employee";

            _MySqlCommand = new MySqlCommand(_QueryString, _MySqlConnection);            
            _MySqlDataAdapter = new MySqlDataAdapter(_MySqlCommand);
            _MySqlDataAdapter.Fill(dataTable);
            _MySqlConnection.Close();
            return dataTable;
        }

        public DataTable GetStaffs(int departmentId)
        {
            var dataTable = new DataTable();

            _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringCentral"].ToString();
            _MySqlConnection = new MySqlConnection(_ConnectionString);
            _MySqlConnection.Open();

            _QueryString =
                "SELECT" +
                "   type_contract," +
                "   employee_status," +
                "   employee_type_id," +
                "   employee.id," +
                "   name," +
                "   namekh," +
                "   photo," +
                "   position," +
                "   position.title as position_title," +
                "   sex," +
                "   shift," +
                "   employee.title " +
                "FROM" +
                "   employee" +
                "   INNER JOIN position on employee.position = position.id " +
                "WHERE" + 
                "   position.department = " + departmentId + 
                "   AND UCASE(employee_status) = 'ACTIVE'";

            _MySqlCommand = new MySqlCommand(_QueryString, _MySqlConnection);
            _MySqlDataAdapter = new MySqlDataAdapter(_MySqlCommand);
            _MySqlDataAdapter.Fill(dataTable);
            _MySqlConnection.Close();
            return dataTable;
        }

        public bool InsertStaff(DataRow dataRow, int serverIdentification)
        {
            switch (serverIdentification)
            {
                case 1:
                    _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringFp"].ToString();
                    break;
                case 2:
                    _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringSocial"].ToString();
                    break;
                case 3:
                    _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringHotel"].ToString();
                    break;
                case 4:
                    _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringMamie"].ToString();
                    break;
                case 5:
                    _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringMeca"].ToString();
                    break;
            }
            _MySqlConnection = new MySqlConnection(_ConnectionString);
            _MySqlConnection.Open();

            _QueryString =
                "INSERT INTO employee(type_contract, employee_status, employee_type_id, id, name, namekh, photo, position, sex, shift, title) VALUES (" +
                "'" + dataRow[Resources.StaffContractType] + "', " +
                "'" + dataRow[Resources.StaffEmploymentStatus] + "', " +
                (!(dataRow[Resources.StaffEmploymentType] is DBNull) ? dataRow[Resources.StaffEmploymentType] : "null") + ", " +
                dataRow[Resources.StaffId] + ", " +
                "'" + dataRow[Resources.StaffName] + "', " +
                "'" + dataRow[Resources.StaffNameKhmer] + "', " +
                "'" + dataRow[Resources.StaffPhoto] + "', " +
                (!(dataRow[Resources.StaffPosition] is DBNull) ? dataRow[Resources.StaffPosition] : "null") + ", " +
                "'" + dataRow[Resources.StaffSex] + "', " +
                (!(dataRow[Resources.StaffShift] is DBNull) ? dataRow[Resources.StaffShift] : "null") + ", " +
                "'" + dataRow[Resources.StaffTitle] + "')";

            _MySqlCommand = new MySqlCommand(_QueryString, _MySqlConnection);
            _MySqlCommand.ExecuteNonQuery();
            _MySqlConnection.Close();
            return true;            
        }

        public bool DeleteStaffs(int serverIdentification)
        {
            switch (serverIdentification)
            {
                case 1:
                    _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringFp"].ToString();
                    break;
                case 2:
                    _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringSocial"].ToString();
                    break;
                case 3:
                    _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringHotel"].ToString();
                    break;
                case 4:
                    _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringMamie"].ToString();
                    break;
                case 5:
                    _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringMeca"].ToString();
                    break;
                default:
                    _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringCentral"].ToString();
                    break;
            }
            _MySqlConnection = new MySqlConnection(_ConnectionString);
            _MySqlConnection.Open();

            _QueryString = "DELETE FROM employee";

            _MySqlCommand = new MySqlCommand(_QueryString, _MySqlConnection);
            _MySqlCommand.ExecuteNonQuery();
            _MySqlConnection.Close();
            return true;
        }
    }
}